<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/* ****************************************************************************
  NAIYUMIE KING WANG JJANG Front End Developer <naiyumie@gmail.com>
  FileType:CRLF, Encording:UTF-8, Tab&Space:4/4
  ------------------------------------------------------------------------
  캐릭터들
**************************************************************************** */

class CharactersM extends N_Model {

	/***************************************************
	 * preprocess
	 *     constructor
	 *     function
	 *     struct and global values
	 ***************************************************/
	var $tableName;
    function __construct() {
        parent::__construct();
    }


	/***************************************************
	 * c
	 ***************************************************/

	# 생성
	function c01($argArray = array()){
		//@@todo 클래스 선택 하게 해야함.
		$querySetArray = array(
			'no_level' => '1',
			'no_hp' => '100',
			'no_attack' => '10',
			'no_defense' => '10'
		);
		$sqlSetStatmentArray = array_merge($querySetArray, $argArray);
		$sqlSetStatment = '';
		$sqlSetStatment = $this->getQuerySetStateMent($sqlSetStatmentArray);
		$sql = sprintf('
			INSERT INTO
				tbl_characters
			SET %1$s;',
			$sqlSetStatment
		);
		$this->db->trans_start();
		$this->db->query($sql, $argArray);
		$this->db->trans_complete();
		if ($this->db->trans_status() === TRUE) {
			$result = TRUE;
		}
		if($result){
			$retVal = TRUE;
		} else {
			$retVal = FALSE;
		}
		return $retVal;
	}


	/***************************************************
	 * r
	 ***************************************************/

	# 목록 조회
	function r01($argArray = array()){
		# 상세
		$whereStatement01 = '';
		if(isset($argArray['pk_uid'])===TRUE){
			$whereStatement01 = sprintf('
				 WHERE pk_uid = %s',
				 $this->db->escape_str($argArray['pk_uid'])
			);
		}

		# 검색
		$whereStatement02 = '';
		$whereKey = '';
		$whereVal = '';
		if(isset($argArray['where'])===TRUE){
			$whereKey = $argArray['where']['whereKey'];
			$whereVal = $argArray['where']['whereVal'];
			if($argArray['where']['whereOpt'] == 'absolute'){
				$whereStatement02 = sprintf('
					WHERE %s = "%s" ',
					$this->db->escape_str($whereKey) , $this->db->escape_str($whereVal)
				);
			} else {
				$whereStatement02 = sprintf('
					WHERE %s LIKE "%%%s%%" ',
					$this->db->escape_str($whereKey) , $this->db->escape_str($whereVal)
				);
			}
		}
		//nlog($whereStatement02);

		# 페이지네이션
		$limitStatement = '';
		if(isset($argArray['paginationCfg']['paginationFirst']) === TRUE && isset($argArray['paginationCfg']['paginationSecond']) === TRUE){
			$limitStatement = sprintf('
				 LIMIT %s, %s',
				 $argArray['paginationCfg']['paginationFirst'], $argArray['paginationCfg']['paginationSecond']
			);
		}
		$orderByStatement = ' ORDER BY pk_uid DESC ';

		# 쿼리
		$sql = sprintf('
			SELECT
				pk_uid,
				uq_name,
				no_level,
				no_hp,
				no_attack,
				no_defense,

				# 입수금
				IFNULL((SELECT
					SUM(tbl_item_money_characters.no_money) AS gain_money_sum
				FROM
					tbl_item_money_characters
				WHERE tbl_item_money_characters.fk_characters_uid = tbl_characters.pk_uid
					AND fg_flow = "gain"),0) -
				IFNULL((SELECT
					SUM(tbl_item_money_characters.no_money) AS lose_money_sum
				FROM
					tbl_item_money_characters
				WHERE tbl_item_money_characters.fk_characters_uid = tbl_characters.pk_uid
					AND fg_flow = "lose"),0) AS no_money,

				# 사용여부
				(SELECT
					tbl_enum.text
				FROM
					tbl_enum
				WHERE tbl_characters.fg_is_live = tbl_enum.code) AS fg_is_live,

				# 소유자
				IFNULL((SELECT
					 tbl_members.uq_member_id
				 FROM tbl_members
				 WHERE
					   tbl_characters.pk_uid = tbl_characters.pk_uid
					   AND tbl_characters.fk_members_pk = tbl_members.pk_uid),"소유자없음") AS `owner_id`,
				IFNULL((SELECT
					 tbl_members.st_name
				 FROM tbl_members
				 WHERE
					   tbl_characters.pk_uid = tbl_characters.pk_uid
					   AND tbl_characters.fk_members_pk = tbl_members.pk_uid),"소유자없음") AS `owner_name`

			FROM
				tbl_characters
			%1$s
			%2$s
			%3$s
			%4$s;',
			$whereStatement01,
			$whereStatement02,
			$orderByStatement,
			$limitStatement
		);
		//nlog($sql);

		# 쿼리실행
		$query = $this->db->query($sql);
		$result = $query->result_array($query);
		if(isset($argArray['pk_uid'])===TRUE){
			$retVal = @$result[0];
		} else {
			$retVal = $result;
		}
		return $retVal;
	}

	# 목록 카운트
	function r02($argArray = array()){
		# 검색조건
		$whereStatement02 = '';
		$whereKey = '';
		$whereVal = '';
		//nlog('model argArray', print_r($argArray,true));
		if(isset($argArray['where'])===TRUE){
			$whereKey = $argArray['where']['whereKey'];
			$whereVal = $argArray['where']['whereVal'];
			if($argArray['where']['whereOpt'] == 'absolute'){
				$whereStatement02 = sprintf('
					WHERE %s = "%s" ',
					$this->db->escape_str($whereKey) , $this->db->escape_str($whereVal)
				);
			} else {
				$whereStatement02 = sprintf('
					WHERE %s LIKE "%%%s%%" ',
					$this->db->escape_str($whereKey) , $this->db->escape_str($whereVal)
				);
			}
		}
		//nlog('r02where>>>'+$whereStatement02);

		# 쿼리
		$sql = sprintf('
			SELECT
				COUNT(pk_uid) AS cnt
			FROM tbl_characters
			%1$s',
			$whereStatement02
		);
		$query = $this->db->query($sql);
		$result = $query->result_array($query);
		$retVal = @$result[0]['cnt'];
		return $retVal;
	}

	# 상세 조회
	function r03($argArray = array()){
		# 쿼리
		$sql = sprintf('
			##############################################################
			#
			# 캐릭터의 스테이터스를 조회 한다.
			#
			##############################################################
			# 실행 쿼리
			SELECT
				tbl_characters.pk_uid                  AS `pk_uid`,
				tbl_characters.uq_name                 AS `uq_name`,
				tbl_characters.no_level                AS `no_level`,

				# 경험치
				tbl_characters.no_experience_point     AS `no_experience_point`,

				# IFNULL((SELECT
				# 	SUM(no_experience_point)
				# FROM
				# 	tbl_characters_experience_point
				# WHERE tbl_characters_experience_point.fk_characters_uid = %1$s
				# 	AND fg_flow = "gain") -
				# (SELECT
				# 	SUM(no_experience_point)
				# FROM
				# 	tbl_characters_experience_point
				# WHERE tbl_characters_experience_point.fk_characters_uid = %1$s
				# 	AND fg_flow = "lose"),0) AS no_experience_point,

				tbl_characters.no_hp                   AS `no_hp`,

				# 공격력 및 장비
				tbl_characters.no_attack               AS `no_attack`,
				(SELECT
					 tbl_item.uq_item_name
				 FROM tbl_item,
					 tbl_inventory,
					 tbl_characters
				 WHERE
					   tbl_characters.pk_uid = %1$s
					   AND tbl_characters.fk_weapon_inventory_uid = tbl_inventory.pk_uid
					   AND tbl_inventory.fk_item_uid = tbl_item.pk_uid) AS `wpn_name`,
				(SELECT
					 tbl_item_ability_weapon.no_attack_point
				 FROM tbl_item_ability_weapon,
					 tbl_inventory,
					 tbl_characters,
					 tbl_item
				 WHERE
					   tbl_characters.pk_uid = %1$s
					   AND tbl_characters.fk_weapon_inventory_uid = tbl_inventory.pk_uid
					   AND tbl_inventory.fk_item_uid = tbl_item.pk_uid
					   AND tbl_item.fk_ability = tbl_item_ability_weapon.pk_uid) AS `wpn_atk`,
				tbl_characters.fk_weapon_inventory_uid AS `wpn_ivn`,

				# 방어력 및 장비
				tbl_characters.no_defense              AS `no_defense`,
				(SELECT
					 tbl_item.uq_item_name
				 FROM tbl_item,
					 tbl_inventory,
					 tbl_characters
				 WHERE
				  tbl_characters.pk_uid = %1$s
					AND tbl_characters.fk_armor_inventory_uid = tbl_inventory.pk_uid
					   AND tbl_inventory.fk_item_uid = tbl_item.pk_uid) AS  `amr_name`,

				tbl_characters.fk_armor_inventory_uid  AS `amr_ivn`,

				(SELECT
					 tbl_item_ability_armor.no_defense_point
				 FROM tbl_item_ability_armor,
					 tbl_inventory,
					 tbl_characters,
					 tbl_item
				 WHERE
					tbl_characters.pk_uid = %1$s
					AND tbl_characters.fk_armor_inventory_uid = tbl_inventory.pk_uid
					AND tbl_inventory.fk_item_uid = tbl_item.pk_uid
					AND tbl_item.fk_ability = tbl_item_ability_armor.pk_uid) AS `amr_def`,

				# 입수금
				IFNULL((SELECT
					SUM(tbl_item_money_characters.no_money) AS gain_money_sum
				FROM
					tbl_item_money_characters
				WHERE tbl_item_money_characters.fk_characters_uid = tbl_characters.pk_uid
					AND fg_flow = "gain"),0) -
				IFNULL((SELECT
					SUM(tbl_item_money_characters.no_money) AS lose_money_sum
				FROM
					tbl_item_money_characters
				WHERE tbl_item_money_characters.fk_characters_uid = tbl_characters.pk_uid
					AND fg_flow = "lose"),0) AS no_money,



				# 마지막 로그인
				(SELECT
					CONCAT(
						tbl_characters.dt_joined,
						" ",
						tbl_characters.tm_joined
					)) AS joined,
				(SELECT
					CONCAT(
						tbl_characters.dt_last_logined,
						" ",
						tbl_characters.tm_last_logined
					)) AS last_logined,

				# 사용 가능여부
				fg_is_live,

				# 소유자
				IFNULL((SELECT
					 tbl_members.uq_member_id
				 FROM tbl_members
				 WHERE
					   tbl_characters.pk_uid = %1$s
					   AND tbl_characters.fk_members_pk = tbl_members.pk_uid),"소유자없음") AS `owner_id`,
				IFNULL((SELECT
					 tbl_members.st_name
				 FROM tbl_members
				 WHERE
					   tbl_characters.pk_uid = %1$s
					   AND tbl_characters.fk_members_pk = tbl_members.pk_uid),"소유자없음") AS `owner_name`
			FROM tbl_characters
				LEFT JOIN tbl_inventory
				  ON (tbl_characters.fk_weapon_inventory_uid = tbl_inventory.pk_uid)
				  WHERE
				  tbl_characters.pk_uid = %1$s;
		',
			$argArray['pk_uid']
		);
		//nlog($sql);

		# 쿼리실행
		$query = $this->db->query($sql);
		$result = $query->result_array($query);

		if(isset($argArray['pk_uid'])===TRUE){
			$retVal = @$result[0];
		} else {
			$retVal = $result;
		}
		return $retVal;
	}

	# fk를 기준으로 캐릭터의 경험치 조회
	function r04($argArray = array()){
		$sql = sprintf('
			SELECT
				tbl_characters.`no_experience_point`
			FROM
				tbl_characters
			WHERE `pk_uid` = %1$s;',
			$argArray['fk_characters_uid']
		);
		$query = $this->db->query($sql);
		$result = $query->result_array($query);
		$retVal = @$result[0]['no_experience_point'];
		if($retVal <= 0){
			$retVal = 0;
		}
		return $retVal;
	}

	/***************************************************
	 * u
	 ***************************************************/

	# PK를 기준으로 업데이트
	function u01($argArray = array()){
		$sqlSetStatment = '';
		$sqlSetStatment = $this->getQuerySetStateMent($argArray);
		$sql = sprintf('
			UPDATE
				tbl_characters
			SET
				%1$s
			WHERE
				pk_uid = %2$s;',
			$sqlSetStatment,
			$this->db->escape_str($argArray['pk_uid'])
		);
		$this->db->trans_start();
		$this->db->query($sql, $argArray);
		$this->db->trans_complete();
		nlog($sql);
		if ($this->db->trans_status() === TRUE) {
			$result = TRUE;
		}
		if($result){
			$retVal = TRUE;
		} else {
			$retVal = FALSE;
		}
		return $retVal;
	}


	/***************************************************
	 * d
	 ***************************************************/

}
/* End of file */